package datatables;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Vector;

import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.ListSelectionModel;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;

import net.proteanit.sql.DbUtils;

public class AbstractTable extends JTable {

	Connection databaseConnection;
	String allDataQuery;
	String primaryKeyColumn;
	int primaryKeyColumnID;
	ResultSet rs = null;
	private String tableName;
	Statement st = null;
	String bufferString;
	int previousSelectedRow = -1;
	int previousSelectedColumn = -1;
	boolean hasPrimaryKey;

	/*
	 * Base Class for all our Tables, could have left the other classes away,
	 * but we did it this way since we weren't sure in the beginning that it
	 * would work with only one table class.
	 * 
	 * This class basically manages all the administration tables dynamically,
	 * by only providing the database-table name.
	 */

	public AbstractTable(String _tableName, Connection _databaseConnection,
			boolean _hasPrimaryKey) {

		tableName = _tableName;
		databaseConnection = _databaseConnection;
		allDataQuery = "SELECT * FROM " + tableName;
		hasPrimaryKey = _hasPrimaryKey;

		this.setCellSelectionEnabled(false);
		try {
			st = databaseConnection.createStatement();
			if (hasPrimaryKey) {
				primaryKeyColumn = getPrimaryKeyColumn();
				rs = st.executeQuery(allDataQuery);

				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {

					if (primaryKeyColumn.equals(rs.getMetaData().getColumnName(
							i + 1))) {
						primaryKeyColumnID = i + 1;

					}
				}
			} else {
				rs = st.executeQuery(allDataQuery);
			}

			// DbUtils a tool/library we downloaded that makes the process of
			// filling a JTable easier.

			setModel(DbUtils.resultSetToTableModel(rs));

			// Two Selectionlisteners that are needed to recognize cell
			// selection changes
			// one would only recognize column changes / row changes, not both

			getSelectionModel().addListSelectionListener(
					new ListSelectionListener() {

						@Override
						public void valueChanged(ListSelectionEvent arg0) {
							if (getSelectedRow() > -1
									&& getSelectedColumn() > -1) {
								if (previousSelectedRow != getSelectedRow()) {
									previousSelectedRow = getSelectedRow();
									bufferString = getCellContent(
											getSelectedColumn(),
											getSelectedRow());
								}

							}
						}
					});

			this.getColumnModel().getSelectionModel()
					.addListSelectionListener(new ListSelectionListener() {

						@Override
						public void valueChanged(ListSelectionEvent arg0) {
							if (getSelectedRow() > -1
									&& getSelectedColumn() > -1) {

								if (previousSelectedColumn != getSelectedColumn()) {
									bufferString = getCellContent(
											getSelectedColumn(),
											getSelectedRow());
									previousSelectedColumn = getSelectedColumn();

								}
							}
						}
					});
			this.getModel().addTableModelListener(new TableChangeListener());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(this, e.getMessage());
		}

	}

	public AbstractTable(TableModel dm) {
		super(dm);
		// TODO Auto-generated constructor stub
	}

	public AbstractTable(TableModel dm, TableColumnModel cm) {
		super(dm, cm);
		// TODO Auto-generated constructor stub
	}

	public AbstractTable(int numRows, int numColumns) {
		super(numRows, numColumns);
		// TODO Auto-generated constructor stub
	}

	public AbstractTable(Vector rowData, Vector columnNames) {
		super(rowData, columnNames);
		// TODO Auto-generated constructor stub
	}

	public AbstractTable(Object[][] rowData, Object[] columnNames) {
		super(rowData, columnNames);
		// TODO Auto-generated constructor stub
	}

	public AbstractTable(TableModel dm, TableColumnModel cm,
			ListSelectionModel sm) {
		super(dm, cm, sm);
		// TODO Auto-generated constructor stub
	}

	public String getTableName() {
		return tableName;
	}

	// Method for deleting a row selected in the admin frame, by using the
	// tables primary key if one exists,
	// otherwise it uses the selected columns content an the not selected column
	// if it is a N*N relation
	public void deleteRow() {
		try {
			if (hasPrimaryKey) {
				st = databaseConnection.createStatement();
				st.executeUpdate("DELETE FROM "
						+ tableName
						+ " WHERE \""
						+ primaryKeyColumn
						+ "\" = '"
						+ getCellContent(primaryKeyColumnID - 1,
								getSelectedRow()) + "'");
			} else {
				int keyColumn;
				if (getSelectedColumn() == 1) {

					keyColumn = 0;

				} else {
					keyColumn = 1;
				}

				st.executeUpdate("DELETE FROM " + tableName + " WHERE \""
						+ getColumnName(keyColumn) + "\" = '"
						+ getCellContent(keyColumn, getSelectedRow())
						+ "' AND \"" + getColumnName(getSelectedColumn())
						+ "\" = '" + bufferString + "'");

			}
			JOptionPane.showMessageDialog(this, "Successfully deleted!");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(this, e.getMessage());
		}
		refreshTable();

	}

	// Method that refreshes the table after every change made
	public void refreshTable() {
		try {
			rs = st.executeQuery(allDataQuery);
			setModel(DbUtils.resultSetToTableModel(rs));
			getModel().addTableModelListener(new TableChangeListener());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(this, e.getMessage());
		}

	}

	// Method to find the name of the pkey-column name, with help from the www
	public String getPrimaryKeyColumn() {
		try {
			st = databaseConnection.createStatement();
			rs = st.executeQuery(

			"SELECT      pg_attribute.attname,   format_type(pg_attribute.atttypid, pg_attribute.atttypmod)	FROM pg_index, pg_class, pg_attribute "
					+ "WHERE  pg_class.oid = '"
					+ tableName
					+ "'::regclass AND  indrelid = pg_class.oid AND  pg_attribute.attrelid = pg_class.oid AND "
					+ "  pg_attribute.attnum = any(pg_index.indkey) AND indisprimary");
			rs.next();
			return rs.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "Error";
		}

	}

	// Method that returns the cell content parsed to a string for each data
	// type
	public String getCellContent(int column, int row) {

		switch (this.getValueAt(row, column).getClass().toString()) {

		case "class java.lang.String":
			return this.getValueAt(row, column).toString();

		case "class java.lang.Float":
			return Float.toString((Float) this.getValueAt(row, column));

		case "class java.sql.Timestamp":
			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.s")
					.format((Timestamp) this.getValueAt(row, column));

		case "class java.sql.Date":
			return new SimpleDateFormat("yyyy-MM-dd").format((Date) this
					.getValueAt(row, column));
		default:
			JOptionPane.showMessageDialog(this, "File-Type unknown!");
			return null;

		}
	}

	// basically the method that listens if a change has been made to a cell and
	// then submitts it to the database
	private class TableChangeListener implements TableModelListener {

		@Override
		public void tableChanged(TableModelEvent e) {
			try {
				String cellContent = getCellContent(getSelectedColumn(),
						getSelectedRow());
				if (!cellContent.equals(bufferString)
						|| cellContent.equals(null)) {
					st = databaseConnection.createStatement();
					String columnName = rs.getMetaData().getColumnName(
							getSelectedColumn() + 1);

					if (hasPrimaryKey) {

						if (!primaryKeyColumn.equals(columnName)) {

							bufferString = getCellContent(
									primaryKeyColumnID - 1, getSelectedRow());

						}

						st.executeUpdate("UPDATE " + tableName + " SET \""
								+ columnName + "\" = '" + cellContent
								+ "' WHERE \"" + primaryKeyColumn + "\" = '"
								+ bufferString + "'");

					} else {

						int keyColumn;
						if (getSelectedColumn() == 1) {

							keyColumn = 0;

						} else {
							keyColumn = 1;
						}

						st.executeUpdate("UPDATE " + tableName + " SET \""
								+ columnName + "\" = '" + cellContent
								+ "' WHERE \"" + getColumnName(keyColumn)
								+ "\" = '"
								+ getCellContent(keyColumn, getSelectedRow())
								+ "' AND \"" + columnName + "\" = '"
								+ bufferString + "'");

					}

					refreshTable();
				}
			} catch (SQLException exception) {
				// TODO Auto-generated catch block
				JOptionPane.showMessageDialog(getParent(),
						exception.getMessage());
				setValueAt(bufferString, getSelectedRow(), getSelectedColumn());
			}
		}
	}

}
